﻿
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using OLTS.DAL;
using OLTS.MODEL;
using OLTS.MODEL.Custom_Model;
namespace OLTS.DATA_SERVICE
{
    public class Export_To_Excel_TimeSheet
    {
        public List<OLTS.MODEL.OTS_TIME_SHEET> getWORK_DETAIL(string strID,int month,int s_date,int e_date)
        {
            var result = new List<OLTS.MODEL.OTS_TIME_SHEET>();
            using (var ctx = new Entities())
            {
                var yr = DateTime.Now.Year;
                var S_Date = new DateTime(yr, month, s_date);
                var E_Date = new DateTime(yr, month, e_date);
                var list = ctx.OTS_TIME_SHEET.Include("OTS_WORK_DETAIL").Include("OTS_LEAVE").Where(x => x.EMPLOYEE_ID == strID && (x.DATE >= S_Date && x.DATE <= E_Date)).OrderBy(x => x.DATE).ToList();

                var listDate = getList5Date(S_Date.AddDays(-1));
                foreach (var o in listDate)
                {
                    var obj = list.Where(x => x.DATE == o.Date).FirstOrDefault();
                    if (obj != null)
                    {
                        result.Add(obj);
                    }
                    else
                    {
                        var objnew = new OLTS.MODEL.OTS_TIME_SHEET();
                        objnew.DATE = o.Date;
                        result.Add(objnew); 
                    }
                }
                 
            }
            return result;
        }

        public List<OLTS.MODEL.OTS_TIME_SHEET> getWORK_DETAIL(string strID, int month)
        {
            var result = new List<OLTS.MODEL.OTS_TIME_SHEET>();
            using (var ctx = new Entities())
            {
                var yr = DateTime.Now.Year;
                var S_Date = new DateTime(yr, month, 1);
                int numberOfDays = DateTime.DaysInMonth(yr, month);
                var E_Date = new DateTime(yr, month, numberOfDays);
                result = ctx.OTS_TIME_SHEET.Include("OTS_WORK_DETAIL").Include("OTS_LEAVE").Where(x => x.EMPLOYEE_ID == strID && (x.DATE >= S_Date && x.DATE <= E_Date)).OrderBy(x => x.DATE).ToList();
                 
            }
            return result;
        }

        private List<DateTime> getList5Date(DateTime sdate)
        {
            var result = new List<DateTime>();
            var dt = sdate;
            for (int i = 0; i < 5; i++)
            {
                dt = dt.AddDays(1);
                result.Add(dt);
            }
            return result;
        }

        public Emp_On_Proj_Detail getSite(string strID)
        {
            var r = new Emp_On_Proj_Detail();
            var result1 = new Emp_On_Proj_Detail();
            using (var ctx = new Entities())
            {
                try
                {
                    var projectAssign = ctx.OTS_ASSIGN_PROJECT.Include("OTS_PROJECT").Include("OTS_EMPLOYEE").Where(x=> x.EMPLOYEE_ID == strID && x.STATUS == true).FirstOrDefault();
                     
                    if (projectAssign != null)
                    {
                        r.JOB_STATUS = "เสร็จแล้ว";
                        var proj = projectAssign.OTS_PROJECT;
                        if (proj != null)
                        {
                            r.PROJECT_ID = proj.ID;
                            r.PROJECT_NAME = proj.NAME;

                            var site = ctx.OTS_SITE.Where(x => x.ID == proj.SITE_ID).FirstOrDefault();
                            if (site != null)
                            {
                                r.SITE_ID = site.ID;
                                r.SITE_NAME = site.NAME;
                                r.TIMESHEET_FORMAT = site.TIMESHEET_FORMAT;
                            }
                        }
                        var emp = projectAssign.OTS_EMPLOYEE;
                        if (emp != null)
                        {
                            r.STAFF_ID = emp.ID;
                            r.STAFF_NAME_EN = emp.NAME_EN;
                            r.STAFF_NAME_TH = emp.NAME_TH;
                            r.STAFF_POSITION = emp.POSITION;
                        }
                        
                        var approved = ctx.OTS_APPROVED.Where(x => x.PROJECT_ID == r.PROJECT_ID).OrderBy(x=> x.SEQ).ToList();
                        int index = 0;
                        foreach (var o in approved)
                        {
                            index = index + 1;
                            if (index == 1) r.PROJECT_OWN = o.APPROVED_BY;
                           
                            if (index == 2) r.PROJECT_OWN_1 = o.APPROVED_BY;

                            if (index == 3) r.PROJECT_OWN_2 = o.APPROVED_BY;
                        }
                    }

                    //var result = (from tb_site in ctx.OTS_SITE
                    //              join tb_proj in ctx.OTS_PROJECT on tb_site.ID equals tb_proj.SITE_ID
                    //              join tb_assg in ctx.OTS_ASSIGN_PROJECT on tb_proj.ID equals tb_assg.PROJECT_ID
                    //              join tb_app in ctx.OTS_APPROVED on tb_proj.ID equals tb_app.PROJECT_ID
                    //              join tb_emp in ctx.OTS_EMPLOYEE on tb_assg.EMPLOYEE_ID equals tb_emp.ID
                    //              where tb_assg.EMPLOYEE_ID == strID
                    //              select new
                    //              {
                    //                  PROJECT_ID = tb_proj.ID,
                    //                  SITE_ID = tb_site.ID,
                    //                  SITE_NAME = tb_site.NAME,
                    //                  PROJECT_NAME = tb_proj.NAME,
                    //                  PROJECT_OWN = tb_app.APPROVED_BY,
                    //                  JOB_STATUS = "เสร็จแล้ว",
                    //                  STAFF_ID = tb_emp.ID,
                    //                  STAFF_NAME_TH = tb_emp.NAME_TH,
                    //                  STAFF_NAME_EN = tb_emp.NAME_EN,
                    //                  STAFF_POSITION = tb_emp.POSITION
                    //              });
                    //if (result.ToList().Count > 0)
                    //{
                    //    result1.PROJECT_ID = result.ToList()[0].PROJECT_ID;
                    //    result1.SITE_ID = result.ToList()[0].SITE_ID;
                    //    result1.PROJECT_OWN = result.ToList()[0].PROJECT_OWN;
                    //    result1.JOB_STATUS = result.ToList()[0].JOB_STATUS;
                    //    result1.STAFF_ID = result.ToList()[0].STAFF_ID;
                    //    result1.STAFF_NAME_TH = result.ToList()[0].STAFF_NAME_TH;
                    //    result1.STAFF_NAME_EN = result.ToList()[0].STAFF_NAME_EN;
                    //    result1.STAFF_POSITION = result.ToList()[0].STAFF_POSITION;
                    //    result1.SITE_NAME = result.ToList()[0].SITE_NAME;
                    //    result1.PROJECT_NAME = result.ToList()[0].PROJECT_NAME;
                    //} 
                }
                catch (Exception ex)
                {
                     
                }
            }
            return r;
        }

    }
}
